渡邊徹志(1984年生まれ・TRONと同い年、栃木県出身)
2009-04 - 2015-03: 東京大学大学院学際情報学府学際情報学専攻総合分析情報学コース博士後期課程坂村・越塚研究室(単位取得満期退学)
2012-04 - 2018-07: YRPユビキタス・ネットワーキング研究所研究員
2018-07 - 2019-02: 無職
2019-02 - 2024-01: 株式会社MaaS Tech Japan CTO
2024-02 - 2024-03: 無職
2024-04 - 2024-03: 株式会社team-7、複数社お手伝い、農業(主にデジタル化、運転手として)
公共交通データのオープン化が進み、従来では実現が難しかった様々な応用が実現され始めている。 本セミナーではGTFS・GTFS-RTデータの基礎とその取り扱い方を解説し、 DuckDBを用いた簡易的なELT環境の構築とクエリ・変換処理、可視化の手段を説明する。 発展的に、異なるデータとの組み合わせ方、インサイトの求め方を説明する。
Tip 1: よくある話
「何を実現したい」から「何が必要か」を考えましょう。手段が目的とならないように・・・。
ELT プロセスと ETL プロセスはどう違うのですか?
https://aws.amazon.com/jp/compare/the-difference-between-etl-and-elt/
大規模なDWH: Data Ware HouseではETLが一般的
中小規模であればETLで十分、というかむしろイイと思い始めた: DuckDBの出現
Data Lakeはどちらにしろ必須: データをとりあえず溜めておく場所: S3、ADLS、GCSが一般的
最近はData Lake Houseという概念もある: DWHとDataLakeを組み合わせたもの、Databricksが提唱
DuckDBで事足りなくなったら、DatabricksのようなData Lake Houseサービスへ
Tip 2: よくある話
正直分析要件は刻々と変化する(≒最初から決まってることなんてない)ので、データ基盤として如何にアジリティが高い環境を準備できるかが勝負どころ
DuckDB as the hub of data ingestions and transformation
Needham, M., Hunger, M., Simons, M. (2024). DuckDB in Action. Manning Publications.
https://duckdb.org/docs/installation
winget install DuckDB.cli
brew install duckdb
https://kepler.gl
# ls -1
ToeiBus-GTFS.zip
agency.txt: 運行会社情報
agency_jp.txt
attributions.txt
calendar.txt: 運行日情報
calendar_dates.txt: 運行日付情報
fare_attributes.txt: 運賃情報
fare_rules.txt: 運賃ルール情報
feed_info.txt: フィード情報
office_jp.txt
routes.txt: 路線情報
shapes.txt: 路線形状情報
stop_times.txt: 時刻表情報
stops.txt: 停留所情報
translations.txt
trips.txt: 便情報-- stops.csvをstopsテーブルに保存する
CREATE OR REPLACE TABLE stops AS SELECT * FROM read_csv('stops.txt');
CREATE OR REPLACE TABLE shapes AS SELECT * FROM read_csv('shapes.txt');
CREATE OR REPLACE TABLE trips AS SELECT * FROM read_csv('trips.txt');CREATE OR REPLACE TABLE stop_times AS SELECT * FROM read_csv(
'stop_times.txt',
header=true,
columns={
'trip_id': 'VARCHAR',
'arrival_time': 'VARCHAR',
'departure_time': 'VARCHAR',
'stop_id': 'VARCHAR',
'stop_sequence': 'INTEGER',
'stop_headsign': 'VARCHAR',
'pickup_type': 'UTINYINT',
'drop_off_type': 'UTINYINT',
'shape_dist_traveled': 'DOUBLE',
'timepoint': 'UTINYINT'
});# Usage: bash GTFS2DuckDB.sh DB_NAME GTFS_URL GTFS_TITLE
#bash ./GTFS2DuckDB.bash gtfs https://api-public.odpt.org/api/v4/files/Toei/data/ToeiBus-GTFS.zip toeibus
#bash ./GTFS2DuckDB.bash gtfs https://api-public.odpt.org/api/v4/files/JR-Freight/data/JR-Freight-Train-GTFS-250.zip jrf
DBNAME=$1
GTFS_URL=$2
TITLE=$3
GTFS_FILE=${GTFS_URL##*/}
echo "Download: $GTFS_FILE"
curl -L -o $TITLE.zip $GTFS_URL
echo "Import: $GTFS_FILE to $TITLE.duckdb"
duckdb $DBNAME.duckdb -c "CREATE SCHEMA IF NOT EXISTS $TITLE"
for basename in `unzip -Z1 $TITLE.zip`; do
filename=${basename%.*}
echo "${filename} to DuckDB"
# stop_timesのみスキーマ自動判定が失敗するので、手動でスキーマ定義を渡す(arrival_time, departure_timeが25:00:00のような24時越えの値を含むため)
if [ $filename = "stop_times" ]; then
unzip -p $TITLE.zip $basename | duckdb $DBNAME.duckdb -c "CREATE OR REPLACE TABLE $TITLE.${filename} AS SELECT * FROM read_csv('/dev/stdin', header=true, columns={ 'trip_id': 'VARCHAR', 'arrival_time': 'VARCHAR', 'departure_time': 'VARCHAR', 'stop_id': 'VARCHAR', 'stop_sequence': 'INTEGER', 'stop_headsign': 'VARCHAR', 'pickup_type': 'UTINYINT', 'drop_off_type': 'UTINYINT', 'shape_dist_traveled': 'DOUBLE', 'timepoint': 'UTINYINT' })"
else
unzip -p $TITLE.zip $basename | duckdb $DBNAME.duckdb -c "CREATE OR REPLACE TABLE $TITLE.${filename} AS SELECT * FROM read_csv('/dev/stdin')"
fi
doneGTFS-RTとは
GTFS realtimeフィードを使用すると、交通事業者は、サービスへの混乱 (駅の閉鎖、路線の運行停止、重大な遅延など)、車両の位置、到着予定時刻に関するリアルタイム情報を消費者に提供できます。https://gtfs.org/ja/documentation/realtime/reference/
ProtocolBufferによるバイナリ形式で提供される
プロトコル バッファーは、構造化データをシリアル化するための Google の言語中立、プラットフォーム中立の拡張可能なメカニズムです。XML を思い浮かべてください。ただし、より小さく、より速く、よりシンプルです。データをどのように構造化するかを一度定義すると、特別に生成されたソース コードを使用して、さまざまな言語を使用して、さまざまなデータ ストリームとの間で構造化データを簡単に読み書きできるようになります。 https://protobuf.dev/
import duckdb
import pandas as pd
from google.transit import gtfs_realtime_pb2
from google.protobuf import json_format
import requests
# %% GTFS-RTを取得してパースする
feed = gtfs_realtime_pb2.FeedMessage()
response = requests.get('https://api-public.odpt.org/api/v4/gtfs/realtime/ToeiBus')
feed.ParseFromString(response.content)
# %% パースしたデータをPython Dictにした上でDataFrameに変換
f = json_format.MessageToDict(feed)
# pd.json_normalizeでentityに含まれるデータのKeyをFlattingした上でDataFrameに変換
df_feed = pd.json_normalize(f["entity"])
# vehicle.timestampをUnixTimeからDateTimeに変換、この際UTCとして扱うようにしておくことで日本時間で扱いやすくなる
df_feed['vehicle.timestamp'] = pd.to_datetime(df_feed['vehicle.timestamp'], unit='s', utc=True)
# %% DuckDBに保存(MotherDuckに保存する場合はmd:gtfsrt?motherduck_token=MD_TOKEN)
con = duckdb.connect("gtfsrt.duckdb")
# 初回だけこちらを実行してください
con.execute("CREATE SCHEMA IF NOT EXISTS toeibus")
con.execute("CREATE TABLE IF NOT EXISTS toeibus.vehicles AS SELECT * FROM df_feed")
# 2回目以降はこちら
con.execute("INSERT INTO toeibus.vehicles SELECT * FROM df_feed")
con.close()-- 出力するデータのスキーマを選択
USE toeibus;
-- stopsをCSVで出力する
COPY (
SELECT * FROM stops
) TO 'stops.csv' WITH (FORMAT CSV, HEADER);
-- TripsをShapesから得られたLineStringのGeoJSONで出力する
COPY (
SELECT * FROM trips
LEFT JOIN (
SELECT
shape_id,
{
"type": 'LineString',
"coordinates": list([shape_pt_lon,shape_pt_lat] ORDER BY shape_pt_sequence ASC)
}::JSON AS "geometry"
FROM shapes GROUP BY shape_id
) AS shapes ON trips.shape_id = shapes.shape_id
) TO 'trips.csv' WITH (FORMAT CSV, HEADER);https://kepler.gl/demoを開いて、出力された ToeiBus-GTFS_stops.csv と ToeiBus-GTFS_trips.csv をD&Dする
停留所と路線形状が可視化される
バス停名なども保持されている